﻿'**************************************************************************
'Description:问题上报、生产准备类(实现类)
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhangll
'create Date:2016.04.23
'Rewriter:2016.04.26
'Rewrite Date:Date:2016.04.27
'**************************************************************************
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.DTO
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports Camstar.WebClient.WebServicesProxy.InSiteWebServices
Imports DatabaseAccess.DBUtility
Imports System.Data.OracleClient

Public Class uLMSProblemBusiness
    Implements uLMSProblemIBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()
    ''' <summary>
    ''' 获取用户
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks>Modify YangJiang 20180724 name->para</remarks>
    Public Function GetEmployee(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSProblemIBusiness.GetEmployee
        'Dim strQuery As String = "SELECT e.employeename,e.fullname,e.employeeid" & vbCrLf & _
        '                           "FROM employee e" & vbCrLf & _
        '                           "LEFT JOIN employeerole er ON er.employeeid = e.employeeid" & vbCrLf & _
        '                           "LEFT JOIN roledef r ON r.roleid = er.roleid" & vbCrLf & _
        '                           "WHERE r.rolename = '" & name & "'" & vbCrLf

        Dim strQuery As String = "SELECT e.employeeid,e.employeename,e.fullname FROM employee e " & vbCrLf & _
                                 "LEFT JOIN sessionvalues se ON se.employeeid=e.employeeid " & vbCrLf & _
                                 "WHERE se.factoryid='" & para("FactoryID") & "' AND SE.WORKCENTERID='" & para("WorkcenterID") & "' " 'Modify YangJiang 20180724
        'Dim strQuery As String = "SELECT e.* FROM employee e LEFT JOIN  sessionvalues s ON s.employeeid=e.employeeid WHERE s.factoryid='" & name & "'"
        GetEmployee = dal.StandardGetDataTable(strQuery)
    End Function
    ''' <summary>
    ''' 获取资源
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetResource() As DataTable Implements uLMSProblemIBusiness.GetResource
        Dim strQuery As String = " select rd.resourceename,rd.description from resourcedef rd"
        GetResource = dal.StandardGetDataTable(strQuery)
    End Function
    ''' <summary>
    ''' 获取资源
    ''' </summary>
    ''' <param name="factoryid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkCenter(ByVal factoryid As String) As DataTable Implements uLMSProblemIBusiness.GetWorkCenter 'tianfw modify 'ByVal factoryid As String' 2017.08.21
        Dim strQuery As New StringBuilder

        strQuery.AppendLine("SELECT WC.WORKCENTERNAME, WC.WORKCENTERID, U.UFCPROBLEMTYPENAME,U.UFCPROBLEMTYPEID ")
        strQuery.AppendLine("FROM WORKCENTER WC ")
        strQuery.AppendLine("LEFT JOIN UFCPROBLEMTYPE U ON U.UFCPROBLEMTYPEID = WC.UFCPROBLEMTYPEID ")
        strQuery.AppendLine("WHERE WC.UFCPROBLEMTYPEID IS NOT NULL ")
        strQuery.AppendFormat(" AND WC.FACTORYID='{0}'", factoryid)
        GetWorkCenter = dal.StandardGetDataTable(strQuery.ToString)
    End Function
    ''' <summary>
    ''' 获取角色
    ''' </summary>
    ''' <param name="name"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRole(ByVal name As String) As DataTable Implements uLMSProblemIBusiness.GetRole

        Dim strQuery As String = "SELECT r.rolename,r.roleid" & vbCrLf & _
                                           "FROM roledef r" & vbCrLf & _
                                           "LEFT JOIN factory f ON f.factoryid = r.factoryid" & vbCrLf & _
                                           "WHERE f.factoryname = '" & name & "'" & vbCrLf
        GetRole = dal.StandardGetDataTable(strQuery)
    End Function

    ''' <summary>
    ''' 获取车间
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFactory() As DataTable Implements uLMSProblemIBusiness.GetFactory
        Dim strSql As String = "SELECT * FROM factory"
        GetFactory = dal.StandardGetDataTable(strSql)
    End Function

    ''' <summary>
    ''' 保存问题
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="strMessage"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveProblemInfo(ByVal para As Dictionary(Of String, String), ByRef strMessage As String) As Boolean Implements uLMSProblemIBusiness.SaveProblemInfo
        SaveProblemInfo = False
        Dim dataEntityList As New List(Of ClientAPIEntity)
        Dim dataEntity As New ClientAPIEntity

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = para("Name")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        If para.ContainsKey("Resource") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Resource"
            dataEntity.ClientDataValue = para("Resource")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcToEmployee") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcToEmployee"
            dataEntity.ClientDataValue = para("ufcToEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If
        If para("strOprType") = "0" Then
            If para.ContainsKey("ufcCreateEmployee") Then
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "ufcCreateEmployee"
                dataEntity.ClientDataValue = para("ufcCreateEmployee")
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)
            End If
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcCreateDate"
            dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If
        If para.ContainsKey("WorkCenter") Then
            If para("WorkCenter") <> "其他" Then 'Modify YangJiang 20180724
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "WorkCenter"
                dataEntity.ClientDataValue = para("WorkCenter")
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)
            End If
        End If
        If para.ContainsKey("Container") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = para("Container")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            dataEntity.ClientOtherValue = "Lot"
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("description") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "description"
            dataEntity.ClientDataValue = para("description")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        'add YangJiang 20180329
        If para.ContainsKey("notes") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "notes"
            dataEntity.ClientDataValue = para("notes")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcContent") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcContent"
            dataEntity.ClientDataValue = para("ufcContent")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If
        If para.ContainsKey("ufcResult") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcResult"
            dataEntity.ClientDataValue = para("ufcResult")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("Factory") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Factory"
            dataEntity.ClientDataValue = para("Factory")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If
        ''tianfw add 2017.08.21 添加factoryid
        'If para.ContainsKey("FactoryID") Then
        '    dataEntity = New ClientAPIEntity
        '    dataEntity.ClientDataName = "FactoryID"
        '    dataEntity.ClientDataValue = para("FactoryID")
        '    dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
        '    dataEntityList.Add(dataEntity)
        'End If
        ''
        If para.ContainsKey("ufcChuLiDate") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcChuLiDate"
            dataEntity.ClientDataValue = para("ufcChuLiDate")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcShenHeDate") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcShenHeDate"
            dataEntity.ClientDataValue = para("ufcShenHeDate")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("UfcShenHeEmployee") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcShenHeEmployee"
            dataEntity.ClientDataValue = para("UfcShenHeEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("WorkflowStepID") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "WorkflowStepID"
            dataEntity.ClientDataValue = para("WorkflowStepID")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcCreateTeam") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcCreateTeam"
            dataEntity.ClientDataValue = para("ufcCreateTeam")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("Product") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Product"
            dataEntity.ClientDataValue = para("Product")
            dataEntity.ClientDataVersion = para("ProductRevision")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.RevisionedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcProblemType") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcProblemType"
            dataEntity.ClientDataValue = para("ufcProblemType")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcwtdj") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcWTDJ"
            dataEntity.ClientDataValue = para("ufcwtdj")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcStatus") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcStatus"
            dataEntity.ClientDataValue = para("ufcStatus")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("SuperviseEmployee") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "SuperviseEmployee"
            dataEntity.ClientDataValue = para("SuperviseEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("SuperviseDate") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "SuperviseDate"
            dataEntity.ClientDataValue = para("SuperviseDate")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("SuperviseOpinion") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "SuperviseOpinion"
            dataEntity.ClientDataValue = para("SuperviseOpinion")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("OperatorEmployee") Then '每次操作人
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "OperatorEmployee"
            dataEntity.ClientDataValue = para("OperatorEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ProblemSource") Then '问题来源 add zhunan 2021.11.24
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ProblemSource"
            dataEntity.ClientDataValue = para("ProblemSource")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If


        Dim api As New CamstarClientAPI(para("userName"), para("passWord"))

        If para("strOprType") = "0" Then
            SaveProblemInfo = api.CreateModelByStandard("ufcProblemMaintDoc", "ufcProblemMaint", "New", dataEntityList, strMessage)
        ElseIf para("strOprType") = "1" Then
            SaveProblemInfo = api.UpdateModelByStandard("ufcProblemMaintDoc", "ufcProblemMaint", para("Name"), "", dataEntityList, strMessage)
        End If
    End Function
    ''' <summary>
    ''' 获取问题列表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>Modify YangJiang 20180628 已完成->已关闭
    Function GetMfgProblemList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSProblemIBusiness.GetMfgProblemList
        Dim strQuery As New StringBuilder
        strQuery.AppendLine("SELECT wc.workcentername,wc.workcenterid,up.ufcproblemname,ce.employeename,ce.fullname,")
        strQuery.AppendLine("up.ufccreatedate,f.factoryname,upt.ufcproblemtypename,r.resourcename,c.containername,")
        strQuery.AppendLine("toe.employeename AS toemployeename,toe.fullname AS tofullname,up.ufcwtdj,ws.workflowstepname,ws.workflowstepid,") '增加工序名称和ID查询 add by tianFW 2018年12月18日
        strQuery.AppendLine("up.ufcchulidate,up.ufcshenhedate,pb.productname,p.productrevision,up.description,up.notes,")
        strQuery.AppendLine("up.ufcljxh,up.ufccontent,up.ufcResult,up.superviseopinion,up.supervisedate,se.fullname svfullname,")
        strQuery.AppendLine(" DECODE (up.ufcstatus,'1',ROUND((SYSDATE-up.ufcchulidate)*24 ,2))overtime,up.ufcwtdj priority,")
        strQuery.AppendLine("CASE WHEN up.ufcstatus=0 THEN '已完成' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus,")
        strQuery.AppendLine("CASE WHEN LOWER(ce.employeename)='' AND up.ufcresult IS NULL THEN 1 ELSE 0 END AS tjrflag,toe.employeeid toemployeeid,up.ufcproblemid,")
        strQuery.AppendLine("HE.employeename shenhename,HE.fullname shenhefullname,up.engineno,up.mfgordername,up.sqcdptype,up.problemfeedbackdetailid,up.feedbacktype,up.problemsource") '增加 up.problemsource add zhunan 2021.11.24
        strQuery.AppendLine("FROM ufcproblem up")
        strQuery.AppendLine("LEFT JOIN workcenter wc ON wc.workcenterid=up.workcenterid")
        strQuery.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = up.WORKFLOWSTEPID")
        strQuery.AppendLine("LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid")
        strQuery.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = up.resourceid")
        strQuery.AppendLine("LEFT JOIN employee toe ON toe.employeeid = up.ufctoemployeeid")
        strQuery.AppendLine("LEFT JOIN product p ON p.productid = up.productid")
        strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid")
        strQuery.AppendLine("LEFT JOIN container c ON c.containerid = up.containerid")
        strQuery.AppendLine("LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid = up.ufcproblemtypeid")
        strQuery.AppendLine("LEFT JOIN factory f ON f.factoryid = up.factoryid")
        strQuery.AppendLine("LEFT JOIN employee se ON se.employeeid = up.superviseemployeeid")
        strQuery.AppendLine("LEFT JOIN EMPLOYEE HE ON HE.EMPLOYEEID=UP.ufcshenheemployeeid")
        strQuery.AppendLine("WHERE")
        If para.Keys.Contains("OverTime") Then
            strQuery.AppendLine("(SYSDATE-up.ufcchulidate)>0 ")
        Else
            strQuery.AppendFormat("(up.ufccreateemployeeid='{0}'or  up.ufctoemployeeid = '{0}' or ( up.ufctoemployeeid is null and wc.workcentername='{1}'))",
                                  para("employeeid"), para("WorkCenter"))
        End If
        'zll 20160422 增加：状态查询
        If (para("status").ToString() = "未处理") Then
            strQuery.AppendLine(" and up.ufcstatus='1'")
        ElseIf (para("status").ToString() = "处理中") Then
            strQuery.AppendLine(" and up.ufcstatus='2'")
        ElseIf (para("status").ToString() = "已完成") Then '关闭
            strQuery.AppendLine(" and up.ufcstatus='0'")
        ElseIf (para("status").ToString() = "处理完") Then '处理完成
            strQuery.AppendLine(" and up.ufcstatus='3'")
        End If
        If para.Keys.Contains("ProblemType") = True Then
            If para("ProblemType") <> "全部" Then
                strQuery.AppendFormat(" and upt.ufcproblemtypename='{0}'", para("ProblemType"))
            End If
        End If
        strQuery.AppendFormat(" AND up.FACTORYID='{0}'", para("FactoryID")) 'tianfw add 2017.08.21
        If para.Keys.Contains("CreateEmployee") = True Then
            strQuery.AppendFormat("AND LOWER(ce.fullname) LIKE '%{0}%'", para("CreateEmployee").ToString.ToLower)
        End If
        If para.Keys.Contains("ToEmployee") = True Then
            strQuery.AppendFormat("AND LOWER(toe.fullname) LIKE '%{0}%'", para("ToEmployee").ToString.ToLower)
        End If
        If para.Keys.Contains("ContainerName") = True Then
            strQuery.AppendFormat("AND LOWER(c.containername) = '{0}'", para("ContainerName").ToString.ToLower)
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery.AppendFormat("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para("ProductName").ToString.ToLower)
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery.AppendFormat("AND up.ufccreatedate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", para("StartDate").ToString)
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery.AppendFormat("AND up.ufccreatedate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", para("EndDate").ToString)
        End If
        strQuery.AppendLine(" ORDER BY up.ufccreatedate DESC") 'add YangJiang 20180723 问题上报按提出时间排序

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery.ToString
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    ''' <summary>
    ''' 获取问题上报单位
    ''' add:Wangjh 20181119
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetUfcProbleType() As DataTable Implements uLMSProblemIBusiness.GetUfcProbleType
        Dim strSql As String = "SELECT *  FROM ufcproblemtype upt "
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
    ''' <summary>
    ''' 获取问题上报类型
    ''' add:Wangjh 20181119
    ''' </summary>
    ''' <param name="name"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetUfcProbleTypeList(ByVal name As String) As DataTable Implements uLMSProblemIBusiness.GetUfcProbleTypeList
        Dim strSql As String = <string>
                                SELECT upl.*  FROM ufcproblemtypeproblemslist  upl
                                LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid=upl.ufcproblemtypeid
                               </string>
        strSql += String.Format("WHERE upt.ufcproblemtypename='{0}'", name)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function


    ''' <summary>
    ''' 获取批次SQL
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerInfoSQL() As String
        Dim strQuery As String = "SELECT c.containername,pb.productname,c.originalqty,p.description,sb.specname," & vbCrLf & _
                                 "p.productrevision,c.qty,c.plannedstartdate,c.plannedcompletiondate,wfs.workflowstepname " & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN currentstatus cs ON cs.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid" & vbCrLf & _
                                "LEFT JOIN producttype pt ON pt.producttypeid = p.producttypeid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = cs.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cs.factoryid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cs.workflowstepid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid" & vbCrLf & _
                                "WHERE c.status<>2 and c.parentcontainerid is null and c.splitfromid is null" & vbCrLf
        Return strQuery
    End Function
    ''' <summary>
    ''' 获取批次信息
    ''' </summary>
    ''' <param name="strContainerName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerInfo(ByVal strContainerName As String) As uMESProcessResult Implements uLMSProblemIBusiness.GetContainerInfo
        Dim strQuery As String = GetContainerInfoSQL()
        strQuery += "AND c.containername = '" & strContainerName & "'" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetContainerInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    Public Function SavePrepareInfo(ByVal para As Dictionary(Of String, String), ByRef strMessage As String) As Boolean Implements uLMSProblemIBusiness.SavePrepareInfo

        Dim dataEntityList As New List(Of ClientAPIEntity)
        Dim dataEntity As New ClientAPIEntity

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = para("Name")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Notes"
        dataEntity.ClientDataValue = para("Title")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        '准备类型
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "ufcLJXH"
        dataEntity.ClientDataValue = para("type")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        '准备工序
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Description"
        dataEntity.ClientDataValue = para("spec")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        If para.ContainsKey("WorkCenter") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "WorkCenter"
            dataEntity.ClientDataValue = para("WorkCenter")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para("toEmployee").ToString().Trim() <> "" Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcToEmployee"
            dataEntity.ClientDataValue = para("toEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcChuLiDate") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcChuLiDate"
            dataEntity.ClientDataValue = para("ufcChuLiDate")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para("Resource").ToString().Trim() <> "" Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Resource"
            dataEntity.ClientDataValue = para("Resource")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcContent") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcContent"
            dataEntity.ClientDataValue = para("ufcContent")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("Container") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "Container"
            dataEntity.ClientDataValue = para("Container")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
            dataEntity.ClientOtherValue = "Lot"
            dataEntityList.Add(dataEntity)
        End If

        If para("strOprType") = "0" Then
            If para.ContainsKey("ufcCreateEmployee") Then
                dataEntity = New ClientAPIEntity
                dataEntity.ClientDataName = "ufcCreateEmployee"
                dataEntity.ClientDataValue = para("ufcCreateEmployee")
                dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
                dataEntityList.Add(dataEntity)
            End If
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcCreateDate"
            dataEntity.ClientDataValue = Now.ToString("yyyy-MM-ddTHH:mm:ss")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcProblemType") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcProblemType"
            dataEntity.ClientDataValue = para("ufcProblemType")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcStatus") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcStatus"
            dataEntity.ClientDataValue = para("ufcStatus")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        Dim api As New CamstarClientAPI(para("userName"), para("passWord"))

        Dim isOK As Boolean = False

        If para("strOprType") = "0" Then
            isOK = api.CreateModelByStandard("ufcProblemMaintDoc", "ufcProblemMaint", "New", dataEntityList, strMessage)
        ElseIf para("strOprType") = "1" Then
            isOK = api.UpdateModelByStandard("ufcProblemMaintDoc", "ufcProblemMaint", para("Name"), "", dataEntityList, strMessage)
        End If

        Return isOK

    End Function

    Public Function GetPrepareList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSProblemIBusiness.GetPrepareList

        Dim strQuery As String = String.Format("SELECT up.ufcproblemid,up.ufcproblemname,up.notes AS title,up.description AS specid,up.containerid,up.ufccontent,up.ufcresult,up.workcenterid," & vbCrLf & _
                                 "up.ufcljxh AS repType,up.ufctoemployeeid,up.ufcchulidate," & vbCrLf & _
                                 "up.resourceid,up.ufccreatedate,up.ufccreateteamid ,upt.ufcproblemtypeid,sb.specname,c.containername,pb.productname,p.description AS prodesc," & vbCrLf & _
                                 "wc.workcentername,toe.fullname AS tofullname," & vbCrLf & _
                                 "toe.employeename AS toemployeename,upt.ufcproblemtypename,ce.employeename AS cemployeename,ce.fullname AS cfullname, " & vbCrLf & _
                                 "CASE WHEN up.ufcstatus=0 THEN '已处理' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' END AS ufcstatus,r.resourcename FROM ufcproblem up " & vbCrLf & _
                                 "LEFT JOIN spec s ON s.specid=up.description " & vbCrLf & _
                                 "LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid " & vbCrLf & _
                                 "LEFT JOIN CONTAINER c ON c.containerid=up.containerid " & vbCrLf & _
                                 "LEFT JOIN product p ON p.productid=c.productid " & vbCrLf & _
                                 "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                                 "LEFT JOIN Workcenter wc ON wc.workcenterid=up.workcenterid " & vbCrLf & _
                                 "LEFT JOIN employee toe ON toe.employeeid = up.ufctoemployeeid " & vbCrLf & _
                                 "LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid " & vbCrLf & _
                                 "LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid = up.ufcproblemtypeid " & vbCrLf & _
                                 "LEFT JOIN resourcedef r ON r.resourceid=up.resourceid " & vbCrLf & _
                                 "WHERE upt.ufcproblemtypename='生产准备' ")

        If para.Keys.Contains("ToWorkCenter") = True Then
            strQuery += "AND wc.workcentername LIKE '%" & para("ToWorkCenter").ToString & "%'" & vbCrLf
        End If

        If para.Keys.Contains("CreateEmployee") = True Then
            strQuery += "AND LOWER(ce.fullname) LIKE '%" & para("CreateEmployee").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ToEmployee") = True Then
            strQuery += "AND LOWER(toe.fullname) LIKE '%" & para("ToEmployee").ToString.ToLower & "%'" & vbCrLf
        End If
        If para.Keys.Contains("ContainerName") = True Then
            strQuery += "AND LOWER(c.containername) = '" & para("ContainerName").ToString.ToLower & "'" & vbCrLf
        End If
        If para.Keys.Contains("specno") = True Then
            strQuery += "AND s.specno = '" & para("specno").ToString & "'" & vbCrLf
        End If
        If para.Keys.Contains("ProductName") = True Then
            strQuery += "AND (LOWER(pb.productname) LIKE '%" & para("ProductName").ToString.ToLower & "%' OR LOWER(p.description) LIKE '%" & para("ProductName").ToString.ToLower & "%')" & vbCrLf
        End If
        If para.Keys.Contains("StartDate") = True Then
            strQuery += "AND up.ufccreatedate >= TO_DATE('" & para("StartDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        If para.Keys.Contains("EndDate") = True Then
            strQuery += "AND up.ufccreatedate <= TO_DATE('" & para("EndDate").ToString & "','yyyy-MM-dd')" & vbCrLf
        End If
        'zll 20160422 增加：状态查询
        If (para("status").ToString() = "未处理") Then
            strQuery += " and up.ufcstatus=1"
            'ElseIf (para("status").ToString() = "处理中") Then
            '    strQuery += " and up.ufcstatus='2'"
        ElseIf (para("status").ToString() = "已处理") Then
            strQuery += " and up.ufcstatus=0"
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strQuery
        Dim result As New uMESProcessResult

        Try
            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
            Dim CommonObject As uMESCommonBusiness = New uMESCommonBusiness()

            For Each row In r.DBTable.Rows
                row("SpecName") = CommonObject.GetSpecNameWithoutProduct(row("SpecName").ToString)
            Next

            result.Result = True
            result.DBTable = r.DBTable
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    Public Function GetToolInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSProblemIBusiness.GetToolInfo
        Dim strQuery As String = String.Format("SELECT t.toolinfoname,t.description,t.productid,t.specid,t.tooltypeid ,t.qty, pb.productname,sb.specname,ty.tooltypename  FROM toolinfo t " & vbCrLf & _
                                                "LEFT JOIN product p ON p.productid=t.productid " & vbCrLf & _
                                                "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                                                "LEFT JOIN spec s ON s.specid=t.specid " & vbCrLf & _
                                                "LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid " & vbCrLf & _
                                                "LEFT JOIN tooltype ty ON ty.tooltypeid=t.tooltypeid " & vbCrLf & _
                                                "WHERE pb.productname='{0}'  AND sb.specname='{1}'", para("ProductName").ToString(), para("specname").ToString())
        Dim result As New uMESProcessResult
        Dim DT As DataTable = dal.GetContainerInfo(strQuery)
        result.Result = True
        result.DBTable = DT.Copy()
        result.RowCount = DT.Rows.Count
        Return result
    End Function

    Public Function FinishPrepareInfo(ByVal para As Dictionary(Of String, String), ByRef strMessage As String) As Boolean Implements uLMSProblemIBusiness.FinishPrepareInfo

        Dim dataEntityList As New List(Of ClientAPIEntity)
        Dim dataEntity As New ClientAPIEntity

        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = para("Name")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        If para.ContainsKey("ufcShenHeDate") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcShenHeDate"
            dataEntity.ClientDataValue = para("ufcShenHeDate")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcResult") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcResult"
            dataEntity.ClientDataValue = para("ufcResult")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcShenHeEmployee") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcShenHeEmployee"
            dataEntity.ClientDataValue = para("ufcShenHeEmployee")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntityList.Add(dataEntity)
        End If

        If para.ContainsKey("ufcStatus") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "ufcStatus"
            dataEntity.ClientDataValue = para("ufcStatus")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        Dim api As New CamstarClientAPI(para("userName"), para("passWord"))

        Dim isOK As Boolean = False

        isOK = api.UpdateModelByStandard("ufcProblemMaintDoc", "ufcProblemMaint", para("Name"), "", dataEntityList, strMessage)

        Return isOK

    End Function

    ''' <summary>
    ''' 获取人员信息
    ''' add:Wangjh 20180118
    ''' </summary>
    ''' <param name="employeeName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEmployeeInfoByName(ByVal employeeName As String) As DataTable Implements uLMSProblemIBusiness.GetEmployeeInfoByName
        Dim strSql As String = String.Format("SELECT * FROM employee e WHERE e.employeename='{0}'", employeeName)
        GetEmployeeInfoByName = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 插入转发信息
    ''' add:Wangjh 20180119
    ''' </summary>
    ''' <param name="Para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsertRelayProblem(ByVal Para As Dictionary(Of String, String)) As Boolean Implements uLMSProblemIBusiness.InsertRelayProblem
        Dim insertSql As String = "INSERT INTO RelayProblem rp (rp.createdate,rp.employeeid,rp.oldtoemployeeid,rp.newtoemployeeid,rp.relayreasonnote,rp.ufcproblemid) VALUES (sysdate,:employeeid,:oldtoemployeeid,:newtoemployeeid,:relayreasonnote,:ufcproblemid)"

        Dim insertPara(4) As OracleParameter

        insertPara(0) = DatabaseHelper.NewParameter("employeeid", Para("EmployeeID"), OracleType.VarChar)
        insertPara(1) = DatabaseHelper.NewParameter("oldtoemployeeid", Para("OldToEmployeeID"), OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter("newtoemployeeid", Para("NewToEmployeeID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter("relayreasonnote", Para("RelayReasonNote"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":ufcproblemid", Para("UfcProblemID"), OracleType.VarChar)

        Dim i As Integer = DatabaseHelper.ExecuteSql(insertSql, insertPara)
        If i > 0 Then
            InsertRelayProblem = True
        Else
            InsertRelayProblem = False
        End If
    End Function

    ''' <summary>
    ''' 获取转发信息
    ''' add：Wangjh 20180119
    ''' </summary>
    ''' <param name="Para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRelayProblemInfo(ByVal Para As Dictionary(Of String, String)) As DataTable Implements uLMSProblemIBusiness.GetRelayProblemInfo
        Dim strSql As String = "SELECT * FROM RelayProblem rp"
        strSql += String.Format(" where rp.Ufcproblemid='{0}'", Para("UfcProblemID"))
        strSql += " ORDER BY rp.createdate DESC"
        GetRelayProblemInfo = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取审核日志
    ''' </summary>
    ''' <param name="problemID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProblemResultRecord(ByVal problemID As String) As OracleDataReader Implements uLMSProblemIBusiness.GetProblemResultRecord
        Dim strSql As String = "SELECT up.ufcstatus,m.objectinstanceid,m.objectname,m.executeaction,m.systemdate,m.txndate,mt.audittrailinfo FROM modelingaudittrailheader m" &
                            " INNER JOIN Modelingaudittrail mt ON mt.modelingaudittrailheaderid=m.modelingaudittrailheaderid" &
                            " LEFT JOIN ufcproblem up ON up.ufcproblemid=m.objectinstanceid"

        strSql += String.Format(" WHERE m.objectinstanceid='{0}'", problemID)
        strSql += " ORDER BY m.txndate ASC"
        GetProblemResultRecord = DatabaseHelper.ExecuteReader(strSql)
    End Function

    ''' <summary>
    ''' 获取问题反馈 add:YangJiang 20180416
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProblemInfo(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSProblemIBusiness.GetProblemInfo

        Dim strSQL As New StringBuilder
        strSQL.AppendLine(" SELECT  up.description ufcProblemName,wc.workcentername,toe.fullname AS toemployeename,ce.fullname employeename,up.ufccreatedate,")
        strSQL.AppendLine(" CASE WHEN ROUND((SYSDATE-up.ufcchulidate)*24 ,2)>0 THEN ROUND((SYSDATE-up.ufcchulidate)*24 ,2)  WHEN ROUND((SYSDATE-up.ufcchulidate)*24 ,2)<=0 THEN 0 END AS delayday,")
        strSQL.AppendLine(" CASE  WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus")
        strSQL.AppendLine(" FROM ufcproblem up")
        strSQL.AppendLine(" LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid = up.ufcproblemtypeid")
        strSQL.AppendLine(" LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid")
        strSQL.AppendLine(" LEFT JOIN resourcedef r ON r.resourceid = up.resourceid")
        strSQL.AppendLine(" LEFT JOIN sessionvalues sv ON sv.employeeid = ce.employeeid")
        strSQL.AppendLine(" LEFT JOIN employee toe ON toe.employeeid = up.ufctoemployeeid")
        strSQL.AppendLine(" LEFT JOIN workcenter wc ON wc.workcenterid = up.workcenterid")
        strSQL.AppendLine(" LEFT JOIN factory f ON f.factoryid = up.factoryid")
        strSQL.AppendLine(" LEFT JOIN product p ON p.productid = up.productid")
        strSQL.AppendLine(" LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid")
        strSQL.AppendLine(" LEFT JOIN container c ON c.containerid = up.containerid WHERE up.ufcstatus <> 0")
        strSQL.AppendFormat(" AND ((up.ufctoemployeeid IS NULL AND up.workcenterid='{0}')", para("WorkCenterID"))
        strSQL.AppendFormat(" OR (up.ufctoemployeeid='{0}' OR up.ufccreateemployeeid='{1}'))", para("EmployeeID"), para("EmployeeID"))
        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strSQL.ToString)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 获取零件进度表的批次工序问题上报信息 create by tianFW 2018年12月19日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProblemInfo_ProgressOfProduct(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSProblemIBusiness.GetProblemInfo_ProgressOfProduct
        Dim strsql As New StringBuilder
        Dim strs_uvale As String =
            <string>
                SELECT wc.workcentername,up.ufccontent,cem.fullname Createfullname,tem.fullname DutyOfficer,
                up.ufcshenhedate,sem.fullname shenhefullname,upt.ufcproblemtypename,up.description,up.ufccreatedate,
                up.ufcchulidate,DECODE(up.ufcWTDJ,0,'特急（1H)',1,'紧急（4H)',2,'紧急（4H)',3,'一般（3天)') ufcWTDJvalue,up.ufcwtdj,
                DECODE(up.ufcstatus,0,'已完成'，1,'未处理',2,'处理中',3,'处理完') ufcstatus,
                DECODE (up.ufcstatus,1,ROUND((SYSDATE-up.ufcchulidate)*24 ,2)) overtime
                FROM ufcproblem up 
                LEFT JOIN Workcenter wc ON wc.workcenterid = up.workcenterid
                LEFT JOIN employee cem ON cem.employeeid = up.ufccreateemployeeid
                LEFT JOIN employee tem ON tem.employeeid = up.ufctoemployeeid
                LEFT JOIN EMPLOYEE sem ON sem.EMPLOYEEID= UP.ufcshenheemployeeid
                LEFT JOIN ufcproblemtype upt ON upt.ufcproblemtypeid = up.ufcproblemtypeid
            </string>
        strsql.AppendLine(strs_uvale)
        strsql.AppendFormat(" WHERE UP.CONTAINERID ='{0}' AND UP.WORKFLOWSTEPID='{1}'", para("ContainerID"), para("WorkFlowStepID"))
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function
End Class
